using System;
using System.Data.SQLite;
using System.IO;
using Xunit;

namespace JGSY.Common.Logging.Tests
{
    public class Testl
    {
        private const string DbFile = "testl.sqlite";
        private const string ConnectionString = "Data Source=" + DbFile + ";Version=3;";

        public Testl()
        {
            if (File.Exists(DbFile))
                File.Delete(DbFile);
            SQLiteConnection.CreateFile(DbFile);
        }

        [Fact]
        public void CanConnectToDatabase()
        {
            using (var conn = new SQLiteConnection(ConnectionString))
            {
                conn.Open();
                Assert.Equal(System.Data.ConnectionState.Open, conn.State);
            }
        }

        [Fact]
        public void CanCreateTable()
        {
            using (var conn = new SQLiteConnection(ConnectionString))
            {
                conn.Open();
                var cmd = conn.CreateCommand();
                cmd.CommandText = "CREATE TABLE test (id INTEGER PRIMARY KEY, name TEXT);";
                cmd.ExecuteNonQuery();
            }
        }

        [Fact]
        public void CanInsertAndQueryData()
        {
            using (var conn = new SQLiteConnection(ConnectionString))
            {
                conn.Open();
                var cmd = conn.CreateCommand();
                cmd.CommandText = "CREATE TABLE test (id INTEGER PRIMARY KEY, name TEXT);";
                cmd.ExecuteNonQuery();
                cmd.CommandText = "INSERT INTO test (name) VALUES ('hello');";
                cmd.ExecuteNonQuery();
                cmd.CommandText = "SELECT name FROM test WHERE id = 1;";
                var result = cmd.ExecuteScalar();
                Assert.Equal("hello", result);
            }
        }

        [Fact]
        public void CanHandleInvalidQuery()
        {
            using (var conn = new SQLiteConnection(ConnectionString))
            {
                conn.Open();
                var cmd = conn.CreateCommand();
                cmd.CommandText = "SELECT * FROM not_exist_table;";
                Assert.Throws<SQLiteException>(() => cmd.ExecuteNonQuery());
            }
        }
    }
}
